When a range contains some numbers stored as text

Numbers stored as numbers are sorted first, and then numbers stored as text. For example, after sorting in ascending order, the range would appear that you have two sets of numbers. Also numbers stored as text sort left to right, character by character. For example 11 appears before 5 because 1 is before 5 in the first character.

Both numbers stored as text and numbers are sorted together. For example, after sorting in ascending order the range would appear to have one set of numbers.

Data	Data type
1	Number
10	Number
11	Number stored as text
5	Number stored as text

Data	Data type
1	Number
5	Number stored as text
10	Number
11	Number stored as text

Show One cell at a time

On the Tools menu, click Options, and then click the Error Checking tab.
Make sure the Enable background error checking and Number stored as text boxes are checked.
Select any cell with a green error indicator in the upper left corner .
Next to the cell, click the button that appears , and then click Convert to Number.

Show A whole range at once

In an empty cell, enter the number 1.
Select the cell, and on the Edit menu, click Copy.
Select the range of numbers stored as text you want to convert.
On the Edit menu, click Paste Special.
Under Operation, click Multiply.
Click OK.
Delete the content of the cell entered in the first step.

Note Some accounting programs display negative values with the negative sign (–) to the right of the value. To convert the text strings to values, you must return all of the characters of the text string except the rightmost character (the negation sign), and then multiply the result by –1. For example, if the value in cell A2 is "156–" the following formula converts the text to the value –156.

Data	Formula
156-	=LEFT(A2,LEN(A2)-1)*-1